package com.geso.code_generator.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.geso.code_generator.model.ColumnInfo;
import com.geso.code_generator.model.TableInfo;

public class JdbcQuery {
	
	private static final String MYSQL_URL = PropertiesUtils.getString("mysql.url");
	private static final String MYSQL_DB = PropertiesUtils.getString("mysql.db");
	private static final String MYSQL_USER_NAME = PropertiesUtils.getString("mysql.user.name");
	private static final String MYSQL_PWD = PropertiesUtils.getString("mysql.pwd");
	
	public static List<TableInfo> getTables(){
		
		List<TableInfo> list = new ArrayList<TableInfo>();
		try {
            Connection con = null; //定义一个MYSQL链接对象
            Class.forName("com.mysql.jdbc.Driver").newInstance(); //MYSQL驱动
            con = DriverManager.getConnection(MYSQL_URL,MYSQL_USER_NAME,MYSQL_PWD); //链接本地MYSQL

            String sql = "SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = ?";
            PreparedStatement ps; //创建声明
            ps = con.prepareStatement(sql);
            ps.setString(1, MYSQL_DB);
            //新增一条数据
            ResultSet res = ps.executeQuery();
            while (res.next()) {
            	TableInfo info = new TableInfo(res.getString("table_name"), res.getString("table_comment"));
            	list.add(info);
            }
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	public static List<ColumnInfo> getTablesColumns(String tableName){
		
		List<ColumnInfo> list = new ArrayList<ColumnInfo>();
		try {
            Connection con = null; //定义一个MYSQL链接对象
            Class.forName("com.mysql.jdbc.Driver").newInstance(); //MYSQL驱动
            con = DriverManager.getConnection(MYSQL_URL,MYSQL_USER_NAME,MYSQL_PWD); //链接本地MYSQL

            String sql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT FROM information_schema.`COLUMNS` "
            		+ "WHERE TABLE_NAME = ? and  TABLE_SCHEMA = ? ORDER BY ORDINAL_POSITION ASC";
            PreparedStatement ps; //创建声明
            ps = con.prepareStatement(sql);
            ps.setString(1, tableName);
            ps.setString(2, MYSQL_DB);
            //新增一条数据
            ResultSet res = ps.executeQuery();
            while (res.next()) {
            	String column_name = res.getString("column_name");
            	String data_type = res.getString("data_type");
            	String column_comment = res.getString("column_comment");
            	ColumnInfo info = new ColumnInfo(column_name, data_type, column_comment);
            	list.add(info);
            }
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	
	public static void main(String[] args) {
		
//		getTables("jdbc:mysql://123.56.158.36:3306/portal", "portal", "root","root");
//		getTablesColumns("jdbc:mysql://123.56.158.36:3306/portal", "root","root", "activity_history");
		
	}
	
}
